
package cn.hpclub.server.service.garrison;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.util.ObjectUtil;
import cn.hpclub.server.util.excel.ExportUtils;

/**
 * @FileName: GarrisonService.java
 * @Author zhl
 * @Description:驻点服务Service
 * @Date 2016年6月27日 下午2:38:57
 * @CopyRight ZTE Corporation
 */
public class GarrisonService{

    private static final Logger   logger  = LoggerFactory.getLogger(GarrisonService.class);
    public static GarrisonService service = new GarrisonService();

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " FROM  " + "garrison_info where 1=1  ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForCount(item);
        }
        sqlExceptSelect += " order by create_time desc ";

        logger.debug("---------sql--------------================SELECT * " + sqlExceptSelect);

        return sqlExceptSelect;
    }

    private String buildQueryForCount(Entry<String, String> item){
        if("start_time".equalsIgnoreCase(item.getKey())){
            String start_time = "'" + item.getValue().replaceAll(" ", "") + " 00:00:00'";
            return " and garrison_time >=" + start_time + " ";
        }
        if("end_time".equalsIgnoreCase(item.getKey())){
            String end_time = "'" + item.getValue().replaceAll(" ", "") + " 23:59:59'";
            return " and garrison_time <=" + end_time + " ";
        }
        if("city_id".equalsIgnoreCase(item.getKey())){
            String city_id = "" + item.getValue().replaceAll(" ", "") + "";
            return " and city_id =" + city_id + " ";
        }
        return " and " + item.getKey() + " like '%" + item.getValue() + "%'";
    }

    /**
     * @describe 获取基础拜访列表的excel文件
     * @param searchInfo
     *            查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = " SELECT * FROM garrison_info WHERE 1=1 ";
        for(Entry<String, String> item : searchInfo.entrySet()){
            sql += buildQueryForCount(item);
        }
        sql += " ORDER BY create_time DESC ";
        List<Record> seq = Db.find(sql);
        String[] excelHeaders = new String[] { "姓名", "手机号", "工号", "地市", "区县", "渠道", "集团编码", "集团名称", "驻点时间", "4G业务办理量",
                "终端办理量", "其他业务办理量", "定位位置", "备注" };
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[] { "cm_name", "cm_phone", "job_number", "city", "district", "channel",
                "group_number", "group_name", "garrison_time", "td_lte_num", "terminal_num", "other_business_num",
                "location", "remark" };
        try{
            exportUtils.exportExcel(keys, seq, "yyyy-MM-dd HH:ss:mm", 1);// 此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        }
        catch(FileNotFoundException e){
            logger.error("GarrisonService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"
                    + e.getMessage());
        }
        catch(IOException ex){
            logger.error("GarrisonService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"
                    + ex.getMessage());
        }
        return workbook;
    }

    /**
     * @describe 获取基础拜访统计数据
     * @param searchInfo
     *            参数列表
     * @return List<Record> 查询所得数据list
     */
    public List<Record> getGarrisonCount(ConcurrentHashMap<String, String> searchInfo){
        String selectSql = " select result.group_count as group_count,result.city_id as city_id,"
                + "result.city as city,result.garrison_count as garrison_count,result.td_lte_num as td_lte_num,"
                + "result.terminal_num as terminal_num,result.other_business_num as other_business_num,"
                + "(result.td_lte_num + result.terminal_num + result.other_business_num) as total_num FROM (SELECT count(*)AS group_count,s.city_id as city_id,s.city as city,sum(s.garrison_count)AS garrison_count,"
                + "sum(td_lte_num) as td_lte_num ,sum(terminal_num)as terminal_num,sum(other_business_num)as other_business_num ";
        String sqlExceptSelect = " FROM (SELECT city_id,city,group_number,count(id) as garrison_count"
                + ",sum(td_lte_num) as td_lte_num ,sum(terminal_num)as terminal_num,sum(other_business_num)as other_business_num FROM "
                + "garrison_info where 1=1  ";
        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForCount(item);
        }
        sqlExceptSelect += "  GROUP BY city_id,group_number) s  GROUP BY s.city_id ) result ";
        String sql = selectSql + sqlExceptSelect;
        List<Record> seq = Db.find(sql);
        return seq;
    }

    public Record getInfoByOrg(int org_id){
        /**
         * 获取organization信息
         */
        Record organization = Db.findById("organization", org_id);
        if(ObjectUtil.isEmpty(organization)){
            return null;
        }
        /**
         * 获取城市/地市Id,名称
         */
        StringBuffer sqlBuffer = new StringBuffer(" SELECT cc.id as city_id,cc.region_name as city_name");
        sqlBuffer.append(" FROM organization o  ");
        sqlBuffer.append(" JOIN  cmcc_city cc ON o.DisplayName = cc.region_name ");
        sqlBuffer.append(" WHERE o.id=? ");
        List<Record> list = Db.find(sqlBuffer.toString(), organization.getInt("CityCode"));
        /**
         * 获取区县信息
         */
        String areaSql = " SELECT DisplayName as displayName FROM  organization where id=?";
        List<Record> areaList = Db.find(areaSql, organization.getInt("AreaCode"));
        if(list == null || list.size() == 0){
            return null;
        } else{
            Record result = list.get(0);
            result.set("channel", organization.getStr("DisplayName"));// 获取渠道数据
            if(ObjectUtil.isNotEmpty(areaList) && areaList.size() > 0){
                Record areaRecord = areaList.get(0);
                result.set("displayName", areaRecord.getStr("displayName"));
            }
            return result;
        }
    }

}
